const express = require('express')
const mysql = require('mysql');
const router = express.Router()
const connection = mysql.createConnection({
  host: 'localhost', port: '3306', user: 'root', password: '123456', database: 'hqh'
})

router.get('/select', (req, res) => {
  const {pageNum, pageSize, name} = req.query
  let sql = ''
  if (Number(pageNum) === 0 && Number(pageSize) === 0) {
    sql = `SELECT id, name, age, gender, CASE WHEN gender = 0 THEN '女' WHEN gender = 1 THEN '男' END AS genderName, DATE_FORMAT(date, '%Y-%m-%d %H:%i:%S') AS date from user WHERE is_show = 1 AND name LIKE '%${name}%' ORDER BY date DESC`
  } else {
    const start = (pageNum - 1) * pageSize
    sql = `SELECT id, name, age, gender, CASE WHEN gender = 0 THEN '女' WHEN gender = 1 THEN '男' END AS genderName, DATE_FORMAT(date, '%Y-%m-%d %H:%i:%S') AS date from user WHERE is_show = 1 AND name LIKE '%${name}%' ORDER BY date DESC LIMIT ${start}, ${pageSize}`
  }
  connection.query(sql, (err, data) => {
    if (!err) {
      const sql2 = `SELECT name from user WHERE is_show = 1 AND name LIKE '%${name}%'`
      connection.query(sql2, (err2, data2) => {
        if (!err2) {
          res.send({
            code: 200, data, total: data2.length, message: 'Success'
          })
        }
      })
    } else {
      throw new Error('error')
    }
  })
})
router.get('/selectDetail', (req, res) => {
  const {id} = req.query
  const sql = `SELECT id, name, age, gender, CASE WHEN gender = 0 THEN '女' WHEN gender = 1 THEN '男' END AS genderName, DATE_FORMAT(date, '%Y-%m-%d %H:%i:%S') AS date from user WHERE id=${id}`
  connection.query(sql, (err, result) => {
    if (!err) {
      res.send({
        code: 200, data: result[0], message: 'Success'
      })
    } else {
      throw new Error('error')
    }
  })
})
router.post('/add', (req, res) => {
  const {name, age, gender} = req.body
  const sql = `INSERT INTO user(name, age, gender, date, is_show) VALUES ('${name}', ${age}, '${gender}', NOW(), 1)`
  connection.query(sql, err => {
    if (!err) {
      res.send({
        code: 200, data: true, message: 'Success'
      })
    } else {
      throw new Error('error')
    }
  })
})
router.post('/update', (req, res) => {
  const {name, age, gender, id} = req.body
  const sql = `UPDATE user SET name='${name}', age=${age}, gender='${gender}' WHERE id=${id}`
  connection.query(sql, err => {
    if (!err) {
      res.send({
        code: 200, data: true, message: 'Success'
      })
    } else {
      throw new Error('error')
    }
  })
})
router.post('/delete', (req, res) => {
  const {id} = req.body
  const sql = `UPDATE user SET is_show=0 WHERE id=${id}`
  connection.query(sql, err => {
    if (!err) {
      res.send({
        code: 200, data: true, message: 'Success'
      })
    } else {
      throw new Error('error')
    }
  })
})

module.exports = router
